import os
import time
import logging
from watchdog.observers import Observer
from watchdog.events import FileSystemEventHandler
import boto3
import yaml
import pymysql

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
limit_upload_counter = 0


class FileChangeHandler(FileSystemEventHandler):

    def __init__(self, config):
        self.config = config
        self.minio_client = boto3.client(
            's3',
            endpoint_url=config['minio']['endpoint'],
            aws_access_key_id=config['minio']['access_key'],
            aws_secret_access_key=config['minio']['secret_key']
        )
        self.db_connection = None
        try:
            self.db_connection = pymysql.connect(
                host=config['database']['host'],
                port=config['database']['port'],
                user=config['database']['user'],
                password=config['database']['password'],
                database=config['database']['db']
            )
            logging.info('Database connection established successfully.')
        except Exception as e:
            logging.error(f'Failed to connect to the database: {e}')
            raise
        self.target_dir_path = config['pdf_file']['path']
        self.select_last_order_sql = config['sql']['select_last_order']
        self.insert_url_sql = config['sql']['insert_url']
        self.select_fields = config['select_fields']
        self.target_fields = config['target_fields']
        self.processed_files = set()

    def on_modified(self, event):
        global limit_upload_counter
        if not event.is_directory and event.src_path.endswith('.pdf'):
            if event.src_path not in self.processed_files:
                self.processed_files.add(event.src_path)
                if limit_upload_counter >= 1:
                    limit_upload_counter = 0
                    self.processed_files.clear()
                else:
                    limit_upload_counter += 1
                    logging.info(f'File {event.src_path} has been modified.')
                    self.upload_to_minio(event.src_path)
                    self.processed_files.clear()

    def upload_to_minio(self, file_path):
        bucket_name = self.config['minio']['bucket']
        # 生成包含年、月、日、时、分的前缀码
        timestamp_prefix = time.strftime('%Y%m%d%H%M_')
        object_name = f'KIC/{timestamp_prefix}{os.path.basename(file_path)}'
        try:
            self.minio_client.upload_file(file_path, bucket_name, object_name)
            url = self.minio_client.generate_presigned_url(
                'get_object',
                Params={'Bucket': bucket_name, 'Key': object_name},
                ExpiresIn=604800  # URL有效期为7天
            )
            self.insert_url_into_db(url)
            logging.info(f'File uploaded to MinIO, URL: {url}')
        except Exception as e:
            logging.error(f'Error uploading file: {e}')

    def insert_url_into_db(self, url):
        # 检查数据库连接是否有效
        if not self.db_connection or not self.db_connection.open:
            self.reconnect_to_database()
        if not self.db_connection:
            logging.error('Database connection is not established.')
            return
        with self.db_connection.cursor() as cursor:
            try:
                select_fields_str = ', '.join(self.select_fields)
                target_fields_str = ', '.join(self.target_fields)
                placeholders_str = ', '.join(['%s'] * (len(self.select_fields) + 1))  # 包括URL
                select_last_order_sql = self.select_last_order_sql.format(select_fields=select_fields_str)
                insert_url_sql = self.insert_url_sql.format(target_fields=target_fields_str,
                                                            placeholders=placeholders_str)
                logging.info("开始查询相关表数据")
                cursor.execute(select_last_order_sql)
                result = cursor.fetchone()
                if result:
                    if isinstance(result, int):
                        result = (result,)
                    data = result + (url,)
                    logging.info("开始执行插入操作")
                    cursor.execute(insert_url_sql, data)
                    self.db_connection.commit()
                    logging.warning("数据插入成功")
                else:
                    logging.warning("No orders found in the order table.")
            except pymysql.MySQLError as e:
                self.db_connection.rollback()
                logging.error(f'Error inserting URL into database: {e}')
                # 在发生错误时尝试重新连接
                self.reconnect_to_database()

    def reconnect_to_database(self):
        """ 尝试重新连接到数据库 """
        try:
            self.db_connection = pymysql.connect(
                host=self.config['database']['host'],
                port=self.config['database']['port'],
                user=self.config['database']['user'],
                password=self.config['database']['password'],
                database=self.config['database']['db']
            )
            logging.info('Reconnected to the database successfully.')
        except Exception as e:
            logging.error(f'Failed to reconnect to the database: {e}')


    def __del__(self):
        if self.db_connection:
            self.db_connection.close()
            logging.info('Database connection closed.')


if __name__ == '__main__':
    try:
        with open('KICConfig.yml', 'r', encoding='utf-8') as file:
            config = yaml.safe_load(file)
        event_handler = FileChangeHandler(config)
        observer = Observer()
        observer.schedule(event_handler, config['pdf_file']['path'], recursive=False)
        observer.start()
        logging.info(f'Starting to monitor {config["pdf_file"]["path"]}...')
        try:
            while True:
                time.sleep(1)
        except KeyboardInterrupt:
            observer.stop()
            logging.info('Stopping observer...')
        observer.join()
    except FileNotFoundError:
        logging.error('Configuration file not found.')
    except yaml.YAMLError as exc:
        logging.error(f'Error in configuration file: {exc}')
    except Exception as e:
        logging.error(f'An unexpected error occurred: {e}')